<?php
    class SQLReaderException extends Exception {}

    /**
     * SQLReader is a class to read a file full of SQL statements and insert
     * them into PHP MySQL commands to be executed.
     */
    class SQLReader 
    {
    
        private $filePath = null;
        private $handle   = null;
        private $openStatus = false;

        /**
         * Default constructer - initialize variables
         * 
         * @param string $filePath - the path to the sql file statements                  
         */                 
        public function __construct($filePath) 
        {
            if (! file_exists($filePath) ) 
            {
                throw new SQLReaderException("File $filePath does not exist!");
            }
            
            $this->filePath = $filePath;
        }
        
        /**
         * Parse the function and return the success of the query
         * 
         * @return bool Whether or not the query was successul.                  
         */                 
        public function parse()
        {
            $this->open();
            $db = DBConnection::inst();

            // Loop through until we hit end-of-file
            $currentQuery = "";
            $delimiter = ";";
            $lineNumber = 0;
            $multiLineComment = false;
            while (! feof($this->handle) )
            {
                // Increment the line counter
                ++$lineNumber;

                // Get the current line from the file, then trim any whitespace
                $line = rtrim(fgets($this->handle));

                if ( ($commentPosition = strpos($line, "--") ) !== false )
                {
                    // If there's a comment, strip everything after it
                    $line = rtrim(substr($line, 0, $commentPosition));
                }

                if ($multiLineComment === false)
                {
                    // No multi line comment so far, test for the syntax
                    if ( ($commentPosition = strpos($line, "/*") ) !== false )
                    {
                        if ( ($endCommentPosition = strpos($line, "*/") ) !== false )
                        {
                            $line = rtrim(substr($line, 0, $commentPosition)) .
                                    rtrim(substr($line, $endCommentPosition + 2));
                        }
                        else
                        {
                            // There is a multi-line open character, remove everything
                            // after this. The multi-line comment spans over
                            // several lines.
                            $line = rtrim(substr($line, 0, $commentPosition));
                            
                            $multiLineComment = true;
                        }
                    }
                }

                else
                {
                    if ( ($commentPosition = strpos($line, "*/") ) !== false )
                    {
                        $line = rtrim(substr($line, $commentPosition + 2) );
                        $multiLineComment = false;
                    }
                    else
                    {
                        continue;
                    }
                }

                if (strlen(trim($line)) == 0)
                {
                    // Blank line, continue and get the next line
                    continue;
                }

                if ( stripos($line, 'delimiter') !== false )
                {
                    // We need to alter the delimiter to the new value specified

                    // Get the new delimiter and store it, 9 is the length of
                    // the word 'delimiter' plus the space.
                    $delimiter = trim(substr($line, 9));
                    $db->query("DELIMITER $delimiter");
                    continue;
                }

                // Append the current line to the query then test to see if
                // the line ends with the delimiter. If so, execute the current
                // query.
                $currentQuery .= " " . $line;                
                $stringLength = strlen($currentQuery);
                
                if (substr($currentQuery, $stringLength - strlen($delimiter) ) == $delimiter )
                {
                    // The line ends with a delimiter. Ensure that we execute
                    // the current line then reset the attributes.
                    try
                    {
                        $currentQuery = substr($currentQuery, 0, $stringLength - strlen($delimiter) ) ;
                        if ($result = $db->query($currentQuery) )
                        {
                            if ( $result instanceof mysqli_result )
                            {
                                $result->close();
                            }
                        }
                        else
                        {
                            throw new SQLReaderException($db->error);
                        }
                        $currentQuery = "";
                    }
                    catch (Exception $e)
                    {
                        throw new SQLReaderException("An unhandled error occured reading $this->filePath on line $lineNumber. " . $e->__toString());
                    }
                }
                else
                {
                    $currentQuery .= "\n";
                }
            }
            $this->close();
            return true;
        }

        /**
         * Opens a file if there's not already a handle open and stores the result
         * in $this->handle.
         *
         * @return bool Whether or not a file was opened
         */
        private function open()
        {
            if ( $this->openStatus === false )
            {
                $this->handle = fopen($this->filePath, 'r');
                $this->openStatus = true;
                return true;
            }
            return false;
        }

        /**
         * Closes any current open connection.
         *
         * @return bool Whether or not the closure was a success
         */
        private function close()
        {
            if ($this->openStatus === true)
            {
                fclose($this->handle);
                $this->openStatus = false;
                return true;
            }
            return false;
        }

    }
?>